<?php
// aggregate.php

function getParm($key,$default) {
    return isset($_GET[$key]) ? $_GET[$key] : $default;
}

// http://www.php.net/manual/en/language.types.float.php#100289
function IsFloatEqual($x,$y,$precision=0.0000001) { 
    return ($x+$precision >= $y) && ($x-$precision <= $y); 
}

function logText($db, $message) {
    $sql = "INSERT INTO `log` (`id`, `timestamp`, `message`) VALUES (NULL, CURRENT_TIMESTAMP, 'test1');";
    $sql = "INSERT INTO `log` (`id`, `timestamp`, `message`) VALUES (NULL, CURRENT_TIMESTAMP, ?);";
    $stmt = $db->prepare($sql);
    $stmt->bind_param('s', $message); 
    $stmt->execute();
    $stmt->close();
}
 
function existHour($db, $readings, $id, $variable, $hour){
    // echo "existHour(db, $readings, $id, $variable, $hour)\n";
    $sql = "SELECT timestamp, value FROM $readings 
WHERE variable = '0014'
AND timespan = 'h'
AND device_id = 1
AND timestamp = '2010-10-05 17'
";
    $sql = "SELECT timestamp, value FROM $readings`
WHERE variable = $variable
AND timespan = 'h'
AND device_id = $id
AND timestamp = $hour
LIMIT 5
";
    $sql = "SELECT timestamp, value  FROM `testreadings` 
WHERE `device_id` = 1 
AND `variable` = '0014'
AND `timestamp` = '2010-10-25 16:00:00' 
AND `timespan` = 'h'";
    $sql = "SELECT timestamp, value FROM $readings`
WHERE variable = ?
AND timespan = 'h'
AND device_id = ?
AND timestamp = ?
";
/*
20101121T130055: grideyeq.com/data/aggregate_test.php 
PHP Warning:  mysqli_stmt::bind_param() [<a href='mysqli-stmt.bind-param'>mysqli-stmt.bind-param</a>]: Number of variables doesn't match number of parameters in prepared statement in /hermes/web09/b283/moo.grideyeqcom/data/aggregate.php on line 46 

I don't understand why I get this error with the query above
 */
    $sql = "SELECT timestamp, value  FROM `testreadings` 
WHERE `device_id` = $id 
AND `variable` = '$variable'
AND `timestamp` = '$hour' 
AND `timespan` = 'h'";
    // echo "$sql\n";
    $stmt = $db->prepare($sql);
    // $stmt->bind_param('sis', $variable, $id, $hour); 
    $stmt->execute();
    $stmt->bind_result($timestamp,$value);
    $exist = false;
    while ($stmt->fetch()) {
        // echo "$timestamp,$value \n";
        $exist = true;
    }
    $stmt->close();
    // echo $exist ? "True" : "False", "\n";
    return $exist;
}

function lastReading($db, $readings, $id, $timespan) {
    // echo "lastReading()\n";
    $sql = "SELECT MAX(timestamp) FROM $readings
WHERE device_id = ?
AND timespan = ?";
    $stmt = $db->prepare($sql);
    $stmt->bind_param('is', $id, $timespan); 
    $stmt->execute();
    $stmt->bind_result($timestamp);
    $stmt->fetch();
    $stmt->close();
    return $timestamp;
}

function getReadings($db, $readings, $id, $datetime) {
    // echo "getReadings(db, $id, $datetime)\n";   
    $sql = "SELECT device_id, variable, timestamp, timespan, value FROM $readings
WHERE device_id = ? 
AND timestamp = ?";
/*
    $sql = "SELECT device_id, variable, timestamp, timespan, value FROM $readings
WHERE device_id = 1
AND timestamp > '2010-10-05 17:59:59'
LIMIT 3";
*/
    $stmt = $db->prepare($sql);
    $stmt->bind_param('is', $id, $datetime); 
    $stmt->execute();
    $stmt->bind_result($dev, $var, $timestamp, $timespan, $val);
    $readings = array();
    while ($stmt->fetch()) {
        echo "$dev $var $timestamp $timespan $val\n";
        $readings[] = array($dev, $var, $timestamp, $timespan, $val);
    }
    $stmt->close();
    return $readings;
}

function getVariables($db, $readings, $id, $datetime) {
    // echo "getVariables()\n";
    // logText($db, "getVariables(db, $readings, $id, $datetime)\n");
    $sql = "SELECT DISTINCT variable FROM $readings
WHERE device_id = ?
AND timestamp = ?
ORDER BY variable ASC";
    $stmt = $db->prepare($sql);
    $stmt->bind_param('is', $id, $datetime); 
    $stmt->execute();
    $stmt->bind_result($variable);
    $vars = array();
    while ($stmt->fetch()) {
        $vars[] = $variable;
    }
    $stmt->close();
    return $vars;
}

function aggregateIfNewHour($db, $readings, $id, $now) {
    // logText($db, "aggregateIfNewHour(db, $readings, $id, $now)\n");
    $lastReading = lastReading($db, $readings, $id, 'r');
    $lastReadingHour = substr($lastReading,0,-6).':00:00';
    // $lastReadingHour = substr($lastReading,0,-4).'0:00';
    $nowHour = substr($now,0,-6).':00:00';
    // $nowHour = substr($now,0,-4).'0:00';
    // logText($db, "$lastReading,$lastReadingHour\n");
    // logText($db, "$now,$nowHour\n");
    if ($lastReadingHour != $nowHour) {
        $variables = getVariables($db, $readings, $id, $lastReading);
        // print_r($variables);
        foreach ($variables as $var) {
            aggregateHour($db, $readings, $id, $var, $lastReadingHour, True);
        }
    }
}

function aggregate24Hours($db, $readings, $id, $datetime) {
    // echo "aggregate24Hours(db, $readings, $id, $datetime)\n";

}

function insertReading($db, $readings, $id, $variable, $time, $timespan, $value) {
    // echo "insertReading(db, $readings, $id, $variable, $time, $timespan, $value)\n";
    $sql = "INSERT INTO $readings (device_id, variable, timestamp, timespan, value) VALUES (?, ?, ?, ?, ?)";
    $sql = "INSERT INTO $readings (device_id, variable, timestamp, timespan, value) 
VALUES ($id, '$variable', '$time', '$timespan', $value)";
    // echo "$sql \n";
    $stmt = $db->prepare($sql); 
    // $stmt->bind_param('isssd', $id, $variable, $time, $timespan, $value); 
    $stmt->execute();
    $stmt->close();
}

function makeTestData($db, $id, $variable, $datetime) {
    $timespan = 'r';
    $value = 0.0;
    $phpdate = strtotime( $datetime );
    $mysqldate = date( 'Y-m-d H:i:s', $phpdate );
    // echo ' ',$datetime,' ', $phpdate,' ', $mysqldate;
    for ($i=0; $i<60; $i++) {
        insertReading($db, $id, $variable, $mysqldate, $timespan, $value);
        $phpdate += 61;
        $mysqldate = date( 'Y-m-d H:i:s', $phpdate );
        $value += 1.1;
    }
}

function testAggregate($db, $readings) {
    // echo "testAggregate(db, $readings)\n";
    logText($db, "testAggregate(db, $readings)\n");
    $id = 1;
    $variable = '0014';
    $datetime = '2010-10-25 16:58:13';
    $hour = '2010-10-25 16:00:00';
    // makeTestTable($db, $datetime);
    // existHour($db, $readings, $id, $variable, $hour);
    aggregateIfNewHour($db, $readings, $id, '2010-10-28 17:00:00');
    // logText($db, "Tea for two\n");
}

function removeTestData($db, $id, $variable) {
    // echo 'removeTestData()', "\n";
    $sql = "DELETE FROM readings
WHERE device_id = ?
AND variable = ?
LIMIT 70";
    $stmt = $db->prepare($sql);
    $stmt->bind_param('is', $id, $variable); 
    $stmt->execute();
    $stmt->close();
}

function updateReading($db, $readings, $id, $variable, $time, $timespan, $value) {
    // echo "updateReading(db, $readings, $id, $variable, $time, $timespan, $value)\n";
    $sql = "UPDATE $readings
SET value = 1.2
WHERE variable = '0014'
AND timespan = 'h'
AND device_id = 1
AND timestamp = '2010-10-05 17'
LIMIT 1
";
    $sql = "UPDATE $readings
SET value = ?
WHERE variable = ?
AND timespan = ?
AND device_id = ?
AND timestamp = ?
LIMIT 1
";
    $stmt = $db->prepare($sql);
    $stmt->bind_param('dssis', $value, $variable, $timespan, $id, $time); 
    $stmt->execute();
    $stmt->close();
}

function calculateHour($db, $readings, $id, $variable, $hour) {
    // echo "calculateHour(db, $readings, $id, $variable, $hour)\n";
    $sql = "SELECT timestamp, value FROM $readings 
WHERE variable = '0014'
AND timespan = 'r'
AND device_id = 1
AND timestamp >= '2010-10-05 17'
AND timestamp < ADDTIME('2010-10-05 17','1:00:00')
ORDER BY timestamp
";
    $sql = "SELECT timestamp, value FROM $readings 
WHERE variable = ?
AND timespan = 'r'
AND device_id = ?
AND timestamp >= ?
AND timestamp < ADDTIME(?,'1:00:00')
ORDER BY timestamp
";
     // http://devzone.zend.com/article/686
    $stmt = $db->prepare($sql);
    $stmt->bind_param('siss', $variable, $id, $hour, $hour); 
    $stmt->execute();
    $stmt->bind_result($timestamp,$value);
    // $starttime = strtotime('2010-10-05 17:00:00');
    $starttime = strtotime($hour);
    $prevtime = $starttime;
    $joules = 0;
    while ($stmt->fetch()) {
        $phptimestamp = strtotime($timestamp);
        $deltaTime = $phptimestamp - $prevtime; 
        $joules += $value * $deltaTime;
        // echo '"'.$timestamp.'",'.$value.",".$phptimestamp."\n";
        $prevtime = $phptimestamp;
    }
    $joules += $value * ($starttime + 3600 - $prevtime);
    // echo $joules, "\n";
    $kwh = $joules * 2.77778e-7;
    // echo $kwh, "\n";
    $stmt->close();
    return $kwh;
}

function aggregateHour($db, $readings, $id, $variable, $hour, $update){
    // echo "aggregateHour(db, $readings, $id, $variable, $hour, update)\n";
    if (existHour($db, $readings, $id, $variable, $hour)) {
        if ($update) {
            $kwh = calculateHour($db, $readings, $id, $variable, $hour);
            updateReading($db, $readings, $id, $variable, $hour, 'h', $kwh);
        }
    }
    else {
        $kwh = calculateHour($db, $readings, $id, $variable, $hour);
        insertReading($db, $readings, $id, $variable, $hour, 'h', $kwh);
    }
}

function testVariableTable($db, $table) {
    $sql = "SELECT device_id, variable, timestamp, timespan, value FROM $table LIMIT 5";
    $stmt = $db->prepare($sql);
    // $stmt->bind_param('is', $id, $datetime); 
    $stmt->bind_param('s', $table); 
    $stmt->execute();
    $stmt->bind_result($dev, $var, $timestamp, $timespan, $val);
    $readings = array();
    while ($stmt->fetch()) {
        echo "$dev $var $timestamp $timespan $val\n";
        $readings[] = array($dev, $var, $timestamp, $timespan, $val);
    }
    $stmt->close();
    return $readings;
}

function testQuery($db, $sql) {
    // echo "sqlCommand(db, $sql)\n";
    $stmt = $db->prepare($sql);
    // $stmt->bind_param('s', $table); 
    $stmt->execute();
    $stmt->bind_result($id, $dev, $var, $timestamp, $timespan, $val);
    while ($stmt->fetch()) {
        echo "$id,$dev,$var,$timestamp,$timespan,$val\n";
    }
    $stmt->close();
}

function sqlCommand($db, $sql) {
    // echo "sqlCommand(db, $sql)\n";
    $stmt = $db->prepare($sql);
    // $stmt->bind_param('s', $table); 
    $stmt->execute();
    $stmt->close();
}

function makeTestTable($db, $datetime) {
    $sql = "CREATE TABLE IF NOT EXISTS testreadings 
SELECT * FROM readings
WHERE timestamp > '$datetime'
AND timestamp < '$datetime' + INTERVAL 3 DAY
ORDER BY timestamp ASC
LIMIT 200000";
    sqlCommand($db, $sql);
    $sql = "ALTER TABLE  `testreadings` ADD PRIMARY KEY (  `id` )";
    sqlCommand($db, $sql);
    $sql = "ALTER TABLE  `testreadings` CHANGE  `id`  `id` BIGINT( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT";
    sqlCommand($db, $sql);
}

?>
